﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.IO;
using System.Data.SqlClient;

namespace template_dll
{
    public partial class Form2 : Form
    {
        private DataTable tbl_inf = new DataTable();
        private DataTable tbl_top = new DataTable();
        private DataTable tbl_date = new DataTable();
        private SqlConnection conn;
        public Form2()
        {
            InitializeComponent();
        }

        private string GetServ(string filename)
        {
            string str = "";
            using (StreamReader streamReader = new StreamReader(filename))
            {
                while (!streamReader.EndOfStream)
                {
                    str += streamReader.ReadLine();
                }
            }
            return str;
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            this.Text = "Загрузка информации в базу";
            conn = new SqlConnection(@"Server=" + GetServ("serv.txt") + ";Database=PharmOLAPDW;Integrated Security=True");
            tbl_date = (new SqlStorProc()).SqlProcExecWithQueryOneTable(conn, "pas_dateUpdate", 30, null);
            dateTimePicker1.Value = Convert.ToDateTime(tbl_date.Rows[0][0].ToString());
            dateTimePicker2.Value = Convert.ToDateTime(tbl_date.Rows[0][1].ToString());

        }

        private void button5_Click(object sender, EventArgs e)
        {
            //- Закрыть форму
            this.Close();
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //- Выбрать файл инфляция
            try
            {
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    tbl_inf.Clear();
                    tbl_inf = ds.Tables[0];
                    cn.Close();
                    textBox2.Text = opf.FileName;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //- Выбрать файл топ 1500
            try
            {
                OpenFileDialog opf = new OpenFileDialog();
                opf.Filter = "Файлы Excel (1997-2007)|*.XLSX;*.XLS";
                if (opf.ShowDialog() == DialogResult.OK)
                {
                    string filename = opf.FileName;
                    string ConStr = String.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data Source={0}; Extended Properties=Excel 8.0;", filename);
                    DataSet ds = new DataSet("EXCEL");
                    OleDbConnection cn = new OleDbConnection(ConStr);

                    if (cn.State == ConnectionState.Closed) cn.Open();
                    DataTable schemaTable = cn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { });
                    string sheet1 = (string)schemaTable.Rows[0].ItemArray[2];
                    string select = String.Format("SELECT * FROM [{0}]", sheet1);
                    OleDbDataAdapter ad = new OleDbDataAdapter(select, cn);
                    ad.Fill(ds);
                    tbl_top.Clear();
                    tbl_top = ds.Tables[0];
                    cn.Close();
                    
                    textBox1.Text = opf.FileName;
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void button5_Click_1(object sender, EventArgs e)
        {
            //- Закрыть форму
            this.Close();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            // - Загрузить инфляцию в базу
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
            par[0].name = "@flag_del";
            par[0].value = 1;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int rr = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_delInfOrTop", 30, par); 

            label3.Text = "";
            int rez = -1;
            if (textBox2.Text != "")
            {
                if (tbl_inf.Rows.Count > 0 )
                {
                    SqlStorProc.sp_params[] sp = new SqlStorProc.sp_params[3];
                    sp[0].name = "@infForMonth";
                    sp[1].name = "@infForYear";
                    sp[2].name = "@dateInf";
                    try
                    {
                        int i = 0;
                        foreach (DataRow row in tbl_inf.Rows)
                        {
                            sp[0].value = Convert.ToDouble(row[0].ToString());
                            sp[1].value =Convert.ToDouble(row[1].ToString());
                            sp[2].value = Convert.ToDateTime(row[2].ToString());
                            rez = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_infIns", 30, sp); 
                            i++;
                        }
                        MessageBox.Show("Загружено " + i.ToString() + " строк");
                        label3.Text = "Инфляция загружена в базу";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Есть ошибки!\n" + ex.Message);
                    }
                }
            }
            else MessageBox.Show("Не выбран файл с данными по инфляции");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            //- Загрузить топ 1500 в базу
            SqlStorProc.sp_params[] par = new SqlStorProc.sp_params[1];
            par[0].name = "@flag_del";
            par[0].value = 0;
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            int rr = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_delInfOrTop", 30, par); 
            label3.Text = "";
            int rez = -1;
            if (textBox1.Text != "")
            {

                if (tbl_top.Rows.Count > 0 )
                {
                    SqlStorProc.sp_params[] sp = new SqlStorProc.sp_params[2];
                    sp[0].name = "@drugcode";
                    sp[1].name = "@drugrate";
                    try
                    {
                        int i = 0;
                        foreach (DataRow row in tbl_top.Rows)
                        {
                            sp[0].value = Convert.ToInt32(row[0].ToString());
                            sp[1].value = Convert.ToInt32(row[1].ToString());
                            rez = (new SqlStorProc()).SqlProcExecNonQuery(conn, "pas_top1500Ins", 30, sp); 
                            i++;
                        }
                        MessageBox.Show("Загружено " + i.ToString() + " строк");
                        label3.Text = "Топ 1500 загружен в базу";
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("Есть ошибки!\n" + ex.Message);
                    }
                }
            }
            else MessageBox.Show("Не выбран файл с данными топ 1500");        }
    }
}
